/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entity.Book;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import util.DatabaseUtil;

/**
 *
 * @author zhang
 */
public class BookDao {
    public List<Book> getAllBook(){           //获取所有图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book");
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
    
    public List<Book> getAllUndeletedBook(){           //获取所有未被删除图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where isdelete=0");
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
    
    
    public List<Book> getUndeletedBookByKeyword(String keyword){           //根据关键字获取未被删除图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where isdelete=0 and (bookname like ? or author like ? or publishhouse like ? or type like ?)");
            ps.setString(1,"%"+keyword+"%");
            ps.setString(2,"%"+keyword+"%");
            ps.setString(3,"%"+keyword+"%");
            ps.setString(4,"%"+keyword+"%");
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
    
    
    public Boolean addBook(String bookName,String author,String publishDate,String publishHouse,String type,String introduction,int mark,float price,String image){
        int i=0;
        Connection con=null;
        PreparedStatement ps=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("insert into book(bookname,author,publishdate,publishhouse,type,introduction,mark,price,cover) values(?,?,?,?,?,?,?,?,?)");
            ps.setString(1,bookName);
            ps.setString(2,author);
            ps.setString(3,publishDate);
            ps.setString(4,publishHouse);
            ps.setString(5,type);
            ps.setString(6,introduction);
            ps.setInt(7,mark);
            ps.setFloat(8,price);
            ps.setString(9,image);
            i=ps.executeUpdate();
            if(i!=0){
                return true;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps,null);
        }
        return false;
    }
    
    public Boolean deleteBook(int id){   //删除图书
        int i=0;
        Connection con=null;
        PreparedStatement ps=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("update book set isdelete=1 where id=?");
            ps.setInt(1,id);
            i=ps.executeUpdate();
            if(i!=0){
                return true;
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps,null);
        }
        return false;
    }
    
    
    
    public Book getBookById(int id){
        Book book=null;
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where id=?");
            ps.setInt(1, id);
            rs=ps.executeQuery();
            while(rs.next()){
                book=new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return book;
    }
    
    public List<Book> getBookByName(String keyword){   //根据书名获取图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where bookname like ? or author like ?");
            ps.setString(1, "%"+keyword+"%");
            ps.setString(2, "%"+keyword+"%");
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
    
    public List<Book> getUndeletedBookByName(String keyword){   //根据书名获取未被删除的图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where (bookname like ? or author like ?) and isdelete=0");
            ps.setString(1, "%"+keyword+"%");
            ps.setString(2, "%"+keyword+"%");
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
    
    
    public List<Book> getBookByType(String type){     //获取指定类型的未被删除的图书
        List<Book> list=new ArrayList<>();
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            con=DatabaseUtil.connect();
            ps=con.prepareStatement("select * from book where type=? and isdelete=0");
            ps.setString(1, type);
            rs=ps.executeQuery();
            while(rs.next()){
                list.add(new Book(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getDate(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getFloat(9),rs.getString(10),rs.getBoolean(11)));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DatabaseUtil.closeConnection(con, ps, rs);
        }
        return list;
    }
}
